package smart.wallet.db;

import java.util.List;

import smart.wallet.R;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.View;
import android.widget.EditText;

public class DBAdapter {

	// Database fields
	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public DBAdapter(Context context) {
		this.context = context;
	}

	public DBAdapter open() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		database.execSQL(" PRAGMA foreign_keys = ON ");
		return this;
	}

	public void close() {
		dbHelper.close();
	}

	public long createAccountCategory(long account_id, String name, long image,
			long color, long type) {
		ContentValues initialValues = createCategoriesContentValues(account_id,
				name, image, color, type);
		return database.insert(DBHelper.CATEGORY_TABLE, null, initialValues);
	}

	public long updateAccountCategory(long category_id, long account_id,
			String name, long image, long color, long type) {
		ContentValues initialValues = createCategoriesContentValues(name,
				image, color, type);

		return database.update(DBHelper.CATEGORY_TABLE, initialValues,
				DBHelper.CATEGORY_ID + "=? AND " + DBHelper.CATEGORY_ACCOUNT_ID
						+ "=?", new String[] { String.valueOf(category_id),
						String.valueOf(account_id) });
	}

	public long logicDeleteAccountCategory(long category_id, long account_id) {
		ContentValues initialValues = createCategoriesContentValues(0);
		return database.update(DBHelper.CATEGORY_TABLE, initialValues,
				DBHelper.CATEGORY_ID + "=? AND " + DBHelper.CATEGORY_ACCOUNT_ID
				+ "=?", new String[] { String.valueOf(category_id),
				String.valueOf(account_id) });
	}

	public long createAccount(String name) {
		ContentValues initialValues = createAccountContentValues(name);
		return database.insert(DBHelper.ACCOUNT_TABLE, null, initialValues);
	}

	public long createRegister(long account_id, float value, long category_id,
			String obs, String date) {
		ContentValues initialValues = createRegisterContentValues(account_id,
				value, category_id, obs, date);
		return database.insert(DBHelper.REGISTER_TABLE, null, initialValues);
	}
	
	public long createAdvice(String name, String rule, String state){
		ContentValues initialValues = createAdviceContentValues(name, rule, state);
		return database.insert(DBHelper.ADVICE_TABLE, null, initialValues);
		
	}

	/*
	 * @Override protected void onDestroy(){ gastoDBHelper.close();
	 * super.onDestroy(); }
	 * 
	 * public void onClick(View v) { int id = v.getId();
	 * 
	 * switch(id){ case R.id.button1:
	 * 
	 * gastoDBHelper.createGasto("gasolina", "imagem_gasolina"); Cursor gastos =
	 * gastoDBHelper.fetchAllGastos(); EditText texto = (EditText)
	 * findViewById(R.id.editText1);
	 * texto.setText(String.valueOf(gastos.getCount())); break;
	 * 
	 * }
	 * 
	 * }
	 * 
	 * 
	 * public boolean updateAtividade(long rowId, String category, String
	 * summary, String description) { ContentValues updateValues =
	 * createContentValues(category, summary, description);
	 * 
	 * return database.update(DATABASE_TABLE, updateValues, KEY_ROWID + "=" +
	 * rowId, null) > 0; }
	 * 
	 * public boolean deleteAtividade(long atv_id) { return
	 * database.delete(DBHelper.ATIVIDADES_TABLE, DBHelper.ATIVIDADES_ID + "=" +
	 * atv_id, null) > 0; }
	 * 
	 * public Cursor fetchAllGastos() { return database.query(DATABASE_TABLE,
	 * new String[] { KEY_ROWID, KEY_NAME, KEY_IMAGE}, null, null, null, null,
	 * null); }
	 */

	public Cursor fetchAllAccounts() {
		Cursor mCursor = database.query(true, DBHelper.ACCOUNT_TABLE,
				new String[] { DBHelper.ACCOUNT_ID, DBHelper.ACCOUNT_NAME },
				null, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor fetchAllAccountCategories(long account_id)
			throws SQLException {
		try {
			Cursor mCursor = database.query(true, DBHelper.CATEGORY_TABLE,
					new String[] { DBHelper.CATEGORY_ID,
							DBHelper.CATEGORY_NAME, DBHelper.CATEGORY_IMAGE,
							DBHelper.CATEGORY_COLOR, DBHelper.CATEGORY_TYPE,
							DBHelper.CATEGORY_ACCOUNT_ID },
					DBHelper.CATEGORY_ACCOUNT_ID + "=" + account_id, null,
					null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}
	
	public Cursor fetchAllValidAccountCategories(long account_id)
			throws SQLException {
		try {
			Cursor mCursor = database.query(true, DBHelper.CATEGORY_TABLE,
					new String[] { DBHelper.CATEGORY_ID,
							DBHelper.CATEGORY_NAME, DBHelper.CATEGORY_IMAGE,
							DBHelper.CATEGORY_COLOR, DBHelper.CATEGORY_TYPE,
							DBHelper.CATEGORY_ACCOUNT_ID },
					DBHelper.CATEGORY_ACCOUNT_ID + "=" + account_id+" AND "+DBHelper.CATEGORY_VALID + "= 1", null,
					null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}
	
	
	public Cursor fetchAllRegisters(long account_id) throws SQLException {
		try {
			Cursor mCursor = database.query(true, DBHelper.REGISTER_TABLE,
					new String[] { DBHelper.REGISTER_ID,
							DBHelper.REGISTER_VALUE,
							DBHelper.REGISTER_CATEGORY_ID,
							DBHelper.REGISTER_DATE, DBHelper.REGISTER_OBS },
					DBHelper.REGISTER_ACCOUNT_ID + "=" + account_id, null,
					null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	public Cursor fetchAllRegistersPlusCategoriesNames(long account_id)
			throws SQLException {
		try {
			Cursor mCursor = database.rawQuery("SELECT r.*, c."
					+ DBHelper.CATEGORY_NAME + " FROM "
					+ DBHelper.REGISTER_TABLE + " r INNER JOIN "
					+ DBHelper.CATEGORY_TABLE + " c ON r."
					+ DBHelper.REGISTER_CATEGORY_ID + "=c."
					+ DBHelper.CATEGORY_ID + " WHERE r."
					+ DBHelper.REGISTER_ACCOUNT_ID + "=?",
					new String[] { String.valueOf(account_id) });

			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	public Cursor fetchSumEachCategory(long account_id) throws SQLException {
		try {
			Cursor mCursor = database.rawQuery("SELECT DISTINCT r."
					+ DBHelper.REGISTER_CATEGORY_ID + ", c."
					+ DBHelper.CATEGORY_NAME + ", c." + DBHelper.CATEGORY_COLOR
					+ ", SUM(" + DBHelper.REGISTER_VALUE + ") AS TOTAL FROM "
					+ DBHelper.REGISTER_TABLE + " r LEFT JOIN "
					+ DBHelper.CATEGORY_TABLE + " c ON c."
					+ DBHelper.CATEGORY_ID + "=r."
					+ DBHelper.REGISTER_CATEGORY_ID + " WHERE r."
					+ DBHelper.REGISTER_ACCOUNT_ID + "=? GROUP BY r."
					+ DBHelper.REGISTER_CATEGORY_ID,
					new String[] { String.valueOf(account_id) });

			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	public Cursor fetchSumEachCategory(long account_id, long category_type)
			throws SQLException {
		try {
			Cursor mCursor = database.rawQuery(
					"SELECT DISTINCT r." + DBHelper.REGISTER_CATEGORY_ID
							+ ", c." + DBHelper.CATEGORY_NAME + ", c."
							+ DBHelper.CATEGORY_COLOR + ", SUM("
							+ DBHelper.REGISTER_VALUE + ") AS TOTAL FROM "
							+ DBHelper.REGISTER_TABLE + " r LEFT JOIN "
							+ DBHelper.CATEGORY_TABLE + " c ON c."
							+ DBHelper.CATEGORY_ID + "=r."
							+ DBHelper.REGISTER_CATEGORY_ID + " WHERE r."
							+ DBHelper.REGISTER_ACCOUNT_ID + "=? AND c."
							+ DBHelper.CATEGORY_TYPE + "=? GROUP BY r."
							+ DBHelper.REGISTER_CATEGORY_ID,
					new String[] { String.valueOf(account_id),
							String.valueOf(category_type) });

			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public Cursor fetchSumCategory(long account_id, long category_id)
			throws SQLException {
		try {
			Cursor mCursor = database.rawQuery("SELECT DISTINCT r."
					+ DBHelper.REGISTER_CATEGORY_ID + ", c."
					+ DBHelper.CATEGORY_NAME + ", c."
					+ DBHelper.CATEGORY_COLOR + ", SUM("+ DBHelper.REGISTER_VALUE + ") AS TOTAL FROM "
					+ DBHelper.REGISTER_TABLE + " r LEFT JOIN "
					+ DBHelper.CATEGORY_TABLE + " c ON c."
					+ DBHelper.CATEGORY_ID + "=r."
					+ DBHelper.REGISTER_CATEGORY_ID + " WHERE r."
					+ DBHelper.REGISTER_ACCOUNT_ID + "=? AND c."
					+ DBHelper.CATEGORY_ID + "=? GROUP BY r."
					+ DBHelper.REGISTER_CATEGORY_ID,
					new String[] { String.valueOf(account_id), String.valueOf(category_id) });

			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	public Cursor fetchAllRegistersPlusCategoriesNames(long account_id,
			long category_type) throws SQLException {
		try {
			Cursor mCursor = database.rawQuery(
					"SELECT r.*, c." + DBHelper.CATEGORY_NAME + " FROM "
							+ DBHelper.REGISTER_TABLE + " r INNER JOIN "
							+ DBHelper.CATEGORY_TABLE + " c ON r."
							+ DBHelper.REGISTER_CATEGORY_ID + "=c."
							+ DBHelper.CATEGORY_ID + " WHERE r."
							+ DBHelper.REGISTER_ACCOUNT_ID + "=? AND c."
							+ DBHelper.CATEGORY_TYPE + "=? ORDER BY r."
							+ DBHelper.REGISTER_DATE,
					new String[] { String.valueOf(account_id),
							String.valueOf(category_type) });

			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public Cursor fetchAllAdvices() {
		try {
			Cursor mCursor = database.query(true, DBHelper.ADVICE_TABLE,
					new String[] { DBHelper.ADVICE_ID, DBHelper.ADVICE_NAME },
					null, null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();
			}
			return mCursor;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	// public Cursor fetchAllRegistersPlusCategoriesNames(long account_id,
	// long category_type, String initial_date, String final_date) throws
	// SQLException {
	// try {
	// Cursor mCursor = database.rawQuery("SELECT r.*, c."
	// + DBHelper.CATEGORY_NAME + " FROM "
	// + DBHelper.REGISTER_TABLE + " r INNER JOIN "
	// + DBHelper.CATEGORY_TABLE + " c ON r."
	// + DBHelper.REGISTER_CATEGORY_ID + "=c."
	// + DBHelper.CATEGORY_ID + " WHERE r."
	// + DBHelper.REGISTER_ACCOUNT_ID + "=? AND c."
	// + DBHelper.CATEGORY_TYPE + "=? AND r."
	// + DBHelper.REGISTER_DATE + "<= " + final_date + " AND r."
	// + DBHelper.REGISTER_DATE + ">= " + initial_date,
	// new String[] { String.valueOf(account_id), String.valueOf(category_type)
	// });
	//
	// if (mCursor != null) {
	// mCursor.moveToFirst();
	// }
	// return mCursor;
	// } catch (Exception e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// return null;
	// }
	// }

	public void updateRegister(long rowId, String value) {
		try {
			ContentValues args = new ContentValues();
			args.put(DBHelper.REGISTER_VALUE, value);
			database.update(DBHelper.REGISTER_TABLE, args, DBHelper.REGISTER_ID
					+ "= " + rowId, null);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deleteRegister(long rowId) {
		try {
			database.delete(DBHelper.REGISTER_TABLE, DBHelper.REGISTER_ID
					+ "= " + rowId, null);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private ContentValues createAccountContentValues(String name) {
		ContentValues values = new ContentValues();
		values.put(DBHelper.ACCOUNT_NAME, name);
		return values;
	}

	private ContentValues createRegisterContentValues(long account_id,
			float value, long category_id, String obs, String date) {
		ContentValues values = new ContentValues();
		values.put(DBHelper.REGISTER_ACCOUNT_ID, account_id);
		values.put(DBHelper.REGISTER_VALUE, value);
		values.put(DBHelper.REGISTER_CATEGORY_ID, category_id);
		values.put(DBHelper.REGISTER_OBS, obs);
		values.put(DBHelper.REGISTER_DATE, date);
		return values;
	}

	private ContentValues createCategoriesContentValues(long account_id,
			String name, long image, long color, long type) {
		ContentValues values = new ContentValues();

		values.put(DBHelper.CATEGORY_ACCOUNT_ID, account_id);
		values.put(DBHelper.CATEGORY_NAME, name);
		values.put(DBHelper.CATEGORY_IMAGE, image);
		values.put(DBHelper.CATEGORY_COLOR, color);
		values.put(DBHelper.CATEGORY_TYPE, type);
		values.put(DBHelper.CATEGORY_VALID, 1);
		return values;
	}

	private ContentValues createCategoriesContentValues(String name,
			long image, long color, long type) {
		ContentValues values = new ContentValues();

		values.put(DBHelper.CATEGORY_NAME, name);
		values.put(DBHelper.CATEGORY_IMAGE, image);
		values.put(DBHelper.CATEGORY_COLOR, color);
		values.put(DBHelper.CATEGORY_TYPE, type);
		return values;
	}
	
	private ContentValues createCategoriesContentValues(long valid) {
		ContentValues values = new ContentValues();

		values.put(DBHelper.CATEGORY_VALID, valid);
		return values;
	}
	
	// ADVISOR RULES
	
	private ContentValues createAdviceContentValues(String name, String rule, String state) {
		ContentValues values = new ContentValues();
		values.put(DBHelper.ADVICE_NAME, name);
		values.put(DBHelper.ADVICE_RULE, rule);
		values.put(DBHelper.ADVICE_STATE, state);
		return values;
	}
	
}
